package cmd

import (
	"fmt"
	"gitee.com/cdsailyj/go-vpn/common/tool"
	"gitee.com/cdsailyj/go-vpn/common/util"
	"gitee.com/cdsailyj/go-vpn/initialize"
	"github.com/shopspring/decimal"
	"os"
	"slices"
	"strconv"
	"strings"
)

type HealthCmd struct{}

type CvdCancer struct {
	ID             int    `db:"ID" json:"ID" form:"ID"`
	OFFICE_NAME    string `db:"OFFICE_NAME" json:"OFFICE_NAME" form:"OFFICE_NAME"`
	DEP_NAME       string `db:"DEP_NAME" json:"DEP_NAME" form:"DEP_NAME"`
	NAME           string `db:"NAME" json:"NAME" form:"NAME"`
	LOGIN_NAME     string `db:"LOGIN_NAME" json:"LOGIN_NAME" form:"LOGIN_NAME"`
	ID_CARD        string `db:"ID_CARD" json:"ID_CARD" form:"ID_CARD"`
	BIRTHDAY       string `db:"BIRTHDAY" json:"BIRTHDAY" form:"BIRTHDAY"`
	AGE            int    `db:"AGE" json:"AGE" form:"AGE"`
	SEX            string `db:"SEX" json:"SEX" form:"SEX"`
	ETHNICITY      string `db:"ETHNICITY" json:"ETHNICITY" form:"ETHNICITY"`
	MOBILE         string `db:"MOBILE" json:"MOBILE" form:"MOBILE"`
	CANCER_TYPE    string `db:"CANCER_TYPE" json:"CANCER_TYPE" form:"CANCER_TYPE"`
	CANCER_NAME    string `db:"CANCER_NAME" json:"CANCER_NAME" form:"CANCER_NAME"`
	DIAGNOSIS_TIME string `db:"DIAGNOSIS_TIME" json:"DIAGNOSIS_TIME" form:"DIAGNOSIS_TIME"`
}

type CvdSlowBreath struct {
	ID          int    `db:"ID" json:"ID" form:"ID"`
	OFFICE_NAME string `db:"OFFICE_NAME" json:"OFFICE_NAME" form:"OFFICE_NAME"`
	NAME        string `db:"NAME" json:"NAME" form:"NAME"`
	SEX         string `db:"SEX" json:"SEX" form:"SEX"`
	BIRTHDAY    string `db:"BIRTHDAY" json:"BIRTHDAY" form:"BIRTHDAY"`
	ID_CARD     string `db:"ID_CARD" json:"ID_CARD" form:"ID_CARD"`
	MOBILE      string `db:"MOBILE" json:"MOBILE" form:"MOBILE"`
	POINT       int    `db:"POINT" json:"POINT" form:"POINT"`
	AGE         int    `db:"AGE" json:"AGE" form:"AGE"`
}

type CvdHealthStats struct {
	ID    int             `db:"ID" json:"ID" form:"ID"`
	AVG   decimal.Decimal `db:"AVG" json:"AVG" form:"AVG"`
	MONTH string          `db:"MONTH" json:"MONTH" form:"MONTH"`
	TYPE  string          `db:"TYPE" json:"TYPE" form:"TYPE"`
}

// 查询未匹配身份证
func (t *HealthCmd) Match() {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel("D:\\download\\excel\\names.xlsx", 0)

	contentArr := make([]string, 0)
	for _, sheet1 := range excelData {
		if sheet1 == nil || len(sheet1) == 0 {
			fmt.Printf("表格数据缺失\n")
			return
		}

		success := 0
		for _, v := range sheet1 {
			v["身份证号"] = strings.ToUpper(v["身份证号"])

			var emp EmpBasicInfo
			err := db.Get(&emp, `SELECT ID, ID_NO, WORK_UNIT_ID, PARENT_WORK_IDS  FROM EMP_BASIC_INFO WHERE ID_NO = :1 AND DEL_FLAG = '0'`, v["身份证号"])
			if err != nil {
				fmt.Printf("查询不到：%#v \t %#v \n", err, v)

				contentArr = append(contentArr, fmt.Sprintf("%s\t%s\t%s", ","+v["身份证号"], v["工作单位"], v["姓名"]))
				continue
			}
			fmt.Printf("查询成功：%#v \t  %#v\n", v, success)
			success++
		}
	}

	os.WriteFile("D:\\download\\excel\\身份证未匹配员工信息台账1.xlsx", []byte(strings.Join(contentArr, "\n")), 0777)
}

// 癌症导入
func (t *HealthCmd) Cancer() {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel("D:\\download\\癌症名单.xlsx", 1)
	sheet1 := excelData["sheet1"]
	if sheet1 == nil || len(sheet1) == 0 {
		fmt.Printf("表格数据缺失\n")
		return
	}

	db.Exec("DELETE FROM CVD_CANCER")
	success := 0
	for _, v := range sheet1 {
		intVal, err := strconv.Atoi(v["确诊时间"])
		if v["确诊时间"] != "" && err == nil {
			v["确诊时间"] = tool.ExcelToDate(float64(intVal), "2006-01-02")
		}

		_, err = db.Exec(util.ReplaceOracleQuestion(`INSERT INTO CVD_CANCER(OFFICE_NAME, DEP_NAME, NAME, LOGIN_NAME, ID_CARD, BIRTHDAY, AGE, SEX, ETHNICITY, MOBILE, CANCER_TYPE, CANCER_NAME, DIAGNOSIS_TIME)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`),
			v["单位"], v["部门"], v["姓名"], v["员工编号"], strings.ToUpper(v["身份证号"]), v["出生日期"], v["年龄"], v["性别"], v["民族"], v["电话"], v["肿瘤癌症种类（良性/恶性）"], v["癌症名称"], v["确诊时间"])
		if err != nil {
			fmt.Printf("修改失败：%#v \t %#v \n", err, v)
			return
		}
		fmt.Printf("修改成功：%#v \t  %#v\n", v, success)
		success++
	}
}

// 慢呼导入
func (t *HealthCmd) SlowBreath() {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel("D:\\download\\慢呼名单.xlsx", 2)
	sheet1 := excelData["sheet1"]
	if sheet1 == nil || len(sheet1) == 0 {
		fmt.Printf("表格数据缺失\n")
		return
	}

	db.Exec("DELETE FROM CVD_SLOW_BREATH")
	success := 0
	for _, v := range sheet1 {
		fmt.Printf("数据 %#v \n", v)

		if len(v["身份证号"]) == 18 {
			v["出身年月"], _ = tool.GetBirthdayByIdCard(v["身份证号"])
		}

		age, _ := tool.GetAgeByBirthday(v["出身年月"])

		_, err := db.Exec(util.ReplaceOracleQuestion(`INSERT INTO CVD_SLOW_BREATH(OFFICE_NAME, NAME, SEX, BIRTHDAY, ID_CARD, MOBILE, POINT, AGE)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)`),
			v["工作单位"], v["姓名"], v["性别"], v["出身年月"], strings.ToUpper(v["身份证号"]), v["联系电话"], v["综合分数"], age)
		if err != nil {
			fmt.Printf("修改失败：%#v \t %#v \n", err, v)
			return
		}
		fmt.Printf("修改成功：%#v \t  %#v\n", v, success)
		success++
	}

}

// 非生产亡人入库
func (t *HealthCmd) DeadTime() {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel("D:\\download\\新疆油田非生产性亡人事件\\dead.xlsx", 0)

	if excelData["2023"] == nil || excelData["2024"] == nil || excelData["2025"] == nil {
		fmt.Printf("表格数据缺失\n")
		return
	}

	yearData := map[string][]map[string]string{"2023": excelData["2023"], "2024": excelData["2024"], "2025": excelData["2025"]}

	success := 0
	for _, arr := range yearData {
		for _, v := range arr {
			fmt.Printf("数据：%#v \n", v)
			var date string
			if strings.Contains(v["事件发生时间"], "日") {
				split := strings.Split(v["事件发生时间"], "日")
				date = strings.ReplaceAll(strings.ReplaceAll(split[0], "年", "-"), "月", "-")
			} else {
				d, err := strconv.Atoi(v["事件发生时间"])
				if err == nil {
					date = tool.ExcelToDate(float64(d), "2006-01-02")
				}
			}
			if len(date) > 0 {
				dateSplit := strings.Split(date, "-")
				if len(dateSplit[1]) == 1 {
					dateSplit[1] = "0" + dateSplit[1]
				}
				if len(dateSplit[2]) == 1 {
					dateSplit[2] = "0" + dateSplit[2]
				}
				date = strings.Join(dateSplit, "-")
			}
			_, err := db.Exec("UPDATE CVD_DEAD_EMP SET DEAD_DATE = :1 WHERE ID_CARD = :2", date, strings.ToUpper(v["身份证号码"]))
			if err != nil {
				fmt.Printf("修改失败：%#v \t %#v \n", err, v)
				return
			}
			fmt.Printf("修改成功：%#v \t  %#v\n", v, success)
			success++
		}
	}

}

// 非生产亡人入库
func (t *HealthCmd) Dead() {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel("D:\\download\\dead.xlsx", 0)

	if excelData["2024年非生产性亡人事件统计"] == nil || excelData["2025年非生产性亡人事件统计"] == nil {
		fmt.Printf("表格数据缺失\n")
		return
	}

	yearData := map[string][]map[string]string{"2023": excelData["2023年非生产性亡人事件统计"], "2024": excelData["2024年非生产性亡人事件统计"], "2025": excelData["2025年非生产性亡人事件统计"]}
	db.Exec("DELETE FROM  CVD_DEAD_EMP WHERE 1=1")

	success := 0
	for year, arr := range yearData {
		for _, v := range arr {
			fmt.Printf("数据：%#v \n", v)
			_, err := db.Exec(util.ReplaceOracleQuestion(`INSERT INTO CVD_DEAD_EMP(OFFICE_NAME, NAME, SEX, AGE, ID_CARD, TYPE, YEAR)
VALUES(?, ?, ?, ?, ?, ?, ?)`),
				v["单位"], v["姓名"], v["性别"], v["年龄"], strings.ToUpper(v["身份证号码"]), v["疾病类型（因病亡人填写）"], year)
			if err != nil {
				fmt.Printf("修改失败：%#v \t %#v \n", err, v)
				return
			}
			fmt.Printf("修改成功：%#v \t  %#v\n", v, success)
			success++
		}
	}

}

func (t *HealthCmd) HeadCta() {
	t.Cta("cta2", "HEAD_CTA")
}
func (t *HealthCmd) CoronaryCta() {
	t.Cta("cta1", "CORONARY_CTA")
}

// CTA体检状态导入
func (t *HealthCmd) Cta(file string, field string) {
	db, _ := initialize.GetYwkDB()

	excelData, _ := tool.ReadExcel(fmt.Sprintf("D:\\download\\%s.xlsx", file), 1)
	sheet1 := excelData["sheet1"]

	var idCards []string
	for _, v := range sheet1 {
		idCards = append(idCards, strings.ToUpper(v["身份证号"]))
	}
	fmt.Printf("体检人数：%#v \n", len(idCards))

	var empAll []EmpBasicInfo
	sql := fmt.Sprintf("SELECT ID, ID_NO, WORK_UNIT_ID, PARENT_WORK_IDS FROM EMP_BASIC_INFO WHERE DEL_FLAG = '0'")
	fmt.Printf("SQL语句：%#v \n", sql)
	err := db.Select(&empAll, sql)
	if err != nil {
		fmt.Printf("查询错误：%#v \n", err)
		return
	}
	fmt.Printf("员工列表：%#v \n", len(empAll))

	for _, emp := range empAll {
		val := 2
		if slices.Contains(idCards, emp.ID_NO.String) {
			val = 1
		}
		_, err = db.Exec(util.ReplaceOracleQuestion(fmt.Sprintf("UPDATE EMP_BASIC_INFO SET %s = ? WHERE ID = ?", field)), val, emp.ID)
		fmt.Printf("修改结果：%#v \t  %#v\n", err, emp)
	}

}

// CTA体检状态导入
func (t *HealthCmd) SecondOffice() {
	db, _ := initialize.GetYwkDB()

	var empAll []EmpBasicInfo
	sql := fmt.Sprintf("SELECT ID, ID_NO, WORK_UNIT_ID, PARENT_WORK_IDS FROM EMP_BASIC_INFO WHERE  WORK_UNIT_ID IS NOT NULL AND  SECOND_OFFICE_ID IS NULL")
	fmt.Printf("SQL语句：%#v \n", sql)
	db.Select(&empAll, sql)

	for _, emp := range empAll {
		levels := officeServiceApp.GetOfficeLevelName(emp.WORK_UNIT_ID.String)
		split := strings.Split(emp.PARENT_WORK_IDS.String, ",")
		secondId := ""
		if len(split) > 2 {
			secondId = split[2]
		}
		if len(levels) > 1 {
			secondId = levels[0].ID
		}
		age, _ := tool.GetAgeByIdCard(emp.ID_NO.String)
		_, err := db.Exec(util.ReplaceOracleQuestion("UPDATE EMP_BASIC_INFO SET SECOND_OFFICE_ID = ? , AGE = ?  WHERE ID = ?"), secondId, age, emp.ID)
		fmt.Printf("修改结果：%#v \t  %#v\n", err, emp)
	}

}

func (t *HealthCmd) HealthStats() {
	db, _ := initialize.GetYwkDB()

	//血压 血糖 总胆固醇 甘油三酯 低密度脂蛋白 肥胖 尿酸 同型半胱氨酸
	types := []string{"血压", "血糖", "总胆固醇", "甘油三酯", "低密度脂蛋白", "尿酸", "同型半胱氨酸", "肥胖"}

	var err error
	for _, typeName := range types {
		var records []CvdHealthStats
		sql := `SELECT
  ROUND( AVG( TO_NUMBER( a.RESULT_VALUE ) ), 4 ) avg,
  SUBSTR( b.CHECK_DATE, 1, 7 ) month ,
  :1 type
FROM
  HEALTH_CHECK_PROJECT a 
  INNER JOIN EMP_HEALTH_CHECK_NEW b ON a.CHECK_ID = b.CHECK_ID 
  
WHERE
  b.CHECK_DATE >= '2024-01-01' 
  AND b.CHECK_DATE IS NOT NULL 
  AND REGEXP_LIKE ( a.RESULT_VALUE, '^[0-9]+(\.[0-9]+)?$' ) 
  AND a.PROJECT_NAME LIKE '%' || :2 || '%'
  AND EXISTS (
	SELECT 1
	FROM CVD_MONITOR c
	WHERE b.id_card = c.id_card
  )
GROUP BY
  SUBSTR( b.CHECK_DATE, 1, 7 )
  ORDER BY  SUBSTR( b.CHECK_DATE, 1, 7 )`

		if typeName == "肥胖" {
			err = db.Select(&records, `SELECT
  ROUND( AVG( TO_NUMBER( a.BMI ) ), 4 ) AVG,
  SUBSTR( a.CLOCKING_TIME, 1, 7 ) month ,
  '肥胖' TYPE
FROM
  EMP_WEIGHT_CLOCKING a  
  INNER JOIN EMP_BASIC_INFO b ON a.EMP_ID = b.ID
WHERE
  a.BMI > 0 
  AND a.BMI < 100 
  AND EXISTS (
	SELECT 1
	FROM CVD_MONITOR c
	WHERE b.id_no = c.id_card
  )
GROUP BY
  SUBSTR( a.CLOCKING_TIME, 1, 7 ) 
ORDER BY
  SUBSTR( a.CLOCKING_TIME, 1, 7 )`)
		} else {
			err = db.Select(&records, sql, typeName, typeName)
		}

		if err != nil {
			fmt.Printf("查询失败：%#v \t %#v  \n", typeName, err)
			continue
		}

		db.Exec("DELETE FROM CVD_HEALTH_STATS  WHERE type = :1", typeName)
		for _, item := range records {
			_, err = db.Exec(util.ReplaceOracleQuestion("INSERT INTO CVD_HEALTH_STATS(TYPE, AVG, MONTH) VALUES(?, ?, ?)"), typeName, item.AVG, item.MONTH)
			fmt.Printf("修改结果：%#v \t  %#v\n", err, item)
		}
	}

}

// 调用接口，通过java项目补全体检信息
func (t *HealthCmd) Project() {

	db, _ := initialize.GetYwkDB()

	var empAll []HealthCheckProject
	sql := fmt.Sprintf(`SELECT a.ID, a.CHECK_ID  FROM  EMP_HEALTH_CHECK_NEW  a  left JOIN  HEALTH_CHECK_PROJECT  b  on a.CHECK_ID = b.CHECK_ID WHERE   a.HOSPITAL_NAME = '克拉玛依市中心医院'  AND  a.CHECK_DATE > '2025-01-01' AND b.CHECK_ID IS NULL ORDER BY a.CHECK_DATE DESC`)
	fmt.Printf("SQL语句：%#v \n", sql)
	err := db.Select(&empAll, sql)
	if err != nil {
		fmt.Printf("查询出错：%#v \n", err)
		return
	}
	fmt.Printf("数据总量：%#v \n", len(empAll))

	token := "c95fa519-9d9e-47e6-8e97-bb2c59be9684"
	headers := map[string]string{"authorization": "Bearer " + token, "X-Auth-Id": "&id:377876909306884096"}
	myFunc := func(v any) {
		emp := v.(HealthCheckProject)
		form := map[string]any{"checkId": emp.CHECK_ID.String, "hospitalCode": 1}
		contents, status, err := tool.HttpPostJson("http://11.71.250.53:30010/aygc-znhpt-sys-cds/0/api/common/departList", form, headers)
		contents2, status2, err2 := tool.HttpPostJson("http://11.71.250.53:30010/aygc-znhpt-sys-cds/0/api/common/projectList", form, headers)
		fmt.Printf("修改结果1：%#v \t %#v \t %#v\n", string(contents), status, err)
		fmt.Printf("修改结果2：%#v \t %#v \t %#v\n", string(contents2), status2, err2)
	}

	task := &util.AsyncTask{ChanLen: 20000, TaskNum: 100}
	task.Start(myFunc)

	for _, v := range empAll {
		task.Chan <- v
	}
	task.Stop()
}
